package com.monkeyboy.utils;

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.jexl2.Expression;
import org.apache.commons.jexl2.JexlContext;
import org.apache.commons.jexl2.JexlEngine;
import org.apache.commons.jexl2.MapContext;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * excel导出工具类
 * 
 * @author lm
 *
 */
public class XNDBExcelUtil {
	private static Logger logger = LoggerFactory.getLogger(XNDBExcelUtil.class);
	/**
	 * list集合每页最大数据个数 用于exportByCommonTemplate,exportBySpecialTemplate
	 */
	private static Integer templatePageSize = null;
	/**
	 * list集合每页最大数据个数 用于exportNoNeedTemplate
	 */
	private static Integer pageSize = null;

	private static JexlEngine jexl = null;

	static {
		jexl = new JexlEngine();
		pageSize = 100000;
		templatePageSize = 5000;
	}

	/**
	 * 通用导出(适用海量数据导出,更高效,不支持有参写法) 无需任何模板
	 * 
	 * @param request
	 * @param response
	 * @param mainTitle
	 *            文件名 如: 测试
	 * @param mapper
	 *            格式 如:
	 *            {beanName.getMethod1().getMethod2()}{beanName.getMethod3()}=创建时间,{beanName.getMethod4()}=更新时间
	 * @param beanName
	 *            mapper中必须包含该值
	 * @param list
	 *            数据集合
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static <T> void exportNoNeedTemplate(HttpServletRequest request, HttpServletResponse response,
			String mainTitle, String mapper, String beanName, List<T> list) throws Exception {
		Map<String, Object> param = getExportDataMap(mainTitle, mapper, beanName, list, true);
		List<List<Object>> contentList = (List<List<Object>>) param.get("contentList");
		List<String> titleName = (List<String>) param.get("titleName");

		Workbook wb = new SXSSFWorkbook(10000);
		Font titleFont = wb.createFont();
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		CellStyle titleCellStyle = wb.createCellStyle();
		titleCellStyle.setFont(titleFont);
		titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

		CellStyle contentCellStyle = wb.createCellStyle();
		contentCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
		int pageIndex = 1;
		for (int i = 0; i < contentList.size(); i = pageSize + i) {
			List<List<Object>> sheetContentList = contentList.subList(i,
					i + pageSize > contentList.size() ? contentList.size() : i + pageSize);
			Sheet sh = wb.createSheet("第" + pageIndex + "页");
			Row titileRow = sh.createRow(0);
			titileRow.setHeight((short) 400);
			for (int j = 0; j < titleName.size(); j++) {
				// sh.setColumnWidth(j, 30*256);
				sh.setColumnWidth(j, 30 * 156);
				Cell titleCell = titileRow.createCell(j);
				titleCell.setCellStyle(titleCellStyle);
				titleCell.setCellValue(titleName.get(j));
			}
			for (int j = 0; j < sheetContentList.size(); j++) {
				Row row = sh.createRow(j + 1);
				row.setHeight((short) 400);
				List<Object> rowContentList = sheetContentList.get(j);
				for (int k = 0; k < rowContentList.size(); k++) {
					Cell cell = row.createCell(k);
					cell.setCellStyle(contentCellStyle);
					cell.setCellValue(rowContentList.get(k) == null ? "" : rowContentList.get(k).toString());
				}
			}
			pageIndex = pageIndex + 1;
		}

		String filename = charEncoding(request, mainTitle + ".xlsx");
		response.setContentType("application/x-msdownload;charset=UTF-8");
		response.setHeader("Content-Disposition", "attachment; filename=" + filename);
		wb.write(response.getOutputStream());
		response.getOutputStream().flush();
	}

	/**
	 * 通用导出(适用海量数据导出,更高效,不支持有参写法) 无需任何模板 (非web环境测试用)
	 * 
	 * @param mainTitle
	 *            文件名 如: 测试
	 * @param mapper
	 *            格式 如:
	 *            {beanName.getMethod1().getMethod2()}{beanName.getMethod3()}=创建时间,{beanName.getMethod4()}=更新时间
	 * @param beanName
	 *            mapper中必须包含该值
	 * @param list
	 *            数据集合
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static <T> void exportNoNeedTemplate(String mainTitle, String mapper, String beanName, List<T> list)
			throws Exception {
		Map<String, Object> param = getExportDataMap(mainTitle, mapper, beanName, list, true);
		List<List<Object>> contentList = (List<List<Object>>) param.get("contentList");
		List<String> titleName = (List<String>) param.get("titleName");

		Workbook wb = new SXSSFWorkbook(10000);
		Font titleFont = wb.createFont();
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		CellStyle titleCellStyle = wb.createCellStyle();
		titleCellStyle.setFont(titleFont);
		titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

		CellStyle contentCellStyle = wb.createCellStyle();
		contentCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
		int pageIndex = 1;
		for (int i = 0; i < contentList.size(); i = pageSize + i) {
			List<List<Object>> sheetContentList = contentList.subList(i,
					i + pageSize > contentList.size() ? contentList.size() : i + pageSize);
			Sheet sh = wb.createSheet("第" + pageIndex + "页");
			Row titileRow = sh.createRow(0);
			titileRow.setHeight((short) 400);
			for (int j = 0; j < titleName.size(); j++) {
				// sh.setColumnWidth(j, 30*256);
				sh.setColumnWidth(j, 30 * 156);
				Cell titleCell = titileRow.createCell(j);
				titleCell.setCellStyle(titleCellStyle);
				titleCell.setCellValue(titleName.get(j));
			}
			for (int j = 0; j < sheetContentList.size(); j++) {
				Row row = sh.createRow(j + 1);
				row.setHeight((short) 400);
				List<Object> rowContentList = sheetContentList.get(j);
				for (int k = 0; k < rowContentList.size(); k++) {
					Cell cell = row.createCell(k);
					cell.setCellStyle(contentCellStyle);
					cell.setCellValue(rowContentList.get(k) == null ? "" : rowContentList.get(k).toString());
				}
			}
			pageIndex = pageIndex + 1;
		}

		File tempTargetFile = new File("d:" + File.separator + mainTitle + ".xls");
		FileOutputStream fos = new FileOutputStream(tempTargetFile);
		wb.write(fos);
	}

	/**
	 * 通用导出(适用少量数据导出) 无需另外配置模板文件 通用 exportTemplate.xlsx
	 * 
	 * @param request
	 * @param response
	 * @param mainTitle
	 *            文件名 如: 测试
	 * @param mapper
	 *            格式 如:
	 *            {beanName.getMethod1().getMethod2()}{beanName.getMethod3(param1,param2)}=创建时间,{beanName.getMethod4()}=更新时间
	 * @param beanName
	 *            mapper中必须包含该值
	 * @param list
	 *            数据集合
	 * @throws Exception
	 */
	// public static <T> void exportByCommonTemplate(HttpServletRequest
	// request,HttpServletResponse response,
	// String mainTitle,String mapper,String beanName,List<T> list)
	// throws Exception{
	// excuteExportByTemplate(request, response,
	// CacheUtil.getProperty("export.root")+File.separator+"exportTemplate.xlsx",
	// mainTitle + ".xlsx", getExportDataMap(mainTitle, mapper, beanName,
	// list,false));
	// }
	//

	/**
	 * 根据特殊模板导出(适用少量数据导出)
	 * 
	 * @param request
	 * @param response
	 * @param templateName
	 * @param outFileName
	 * @param param
	 * @throws Exception
	 */
	public static void exportBySpecialTemplate(HttpServletRequest request, HttpServletResponse response,
			String templateName, String outFileName, Map<String, Object> param) throws Exception {
		excuteExportByTemplate(request, response, templateName, outFileName, param);
	}

	/**
	 * 执行导出操作
	 * 
	 * @param request
	 * @param response
	 * @param templateName
	 * @param outFileName
	 * @param param
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private static void excuteExportByTemplate(HttpServletRequest request, HttpServletResponse response,
			String templateName, String outFileName, Map<String, Object> param) throws Exception {
		logger.info("=============模板导出开始   模板路径:{}  =================", templateName);

		String filename = charEncoding(request, outFileName);

		List<List<Object>> objects = new ArrayList<List<Object>>();
		List<String> newSheetNames = new ArrayList<String>();
		String beanName = null;

		Map<String, Object> beanParams = new HashMap<String, Object>();

		Set<String> keySet = param.keySet();
		for (String key : keySet) {
			Object obj = param.get(key);
			if (obj instanceof List && !key.equals("titleName")) {
				List<Object> objList = (List<Object>) obj;
				if (objList != null && !objList.isEmpty()) {
					for (int i = 0; i < objList.size(); i = i + templatePageSize) {
						objects.add(objList.subList(i,
								i + templatePageSize > objList.size() ? objList.size() : i + templatePageSize));
					}
					for (int i = 0; i < objects.size(); i++) {
						newSheetNames.add("第" + (i + 1) + "页");
					}
					beanName = key;
					continue;
				}
			}
			beanParams.put(key, obj);
		}
		Workbook workbook = null;
		FileInputStream inputStream = new FileInputStream(templateName);
		XLSTransformer transformer = new XLSTransformer();

		logger.info("输入参数beanParams:{}", beanParams);
		logger.info("输入参数newSheetNames:{}", newSheetNames);
		if (newSheetNames.isEmpty()) {
			workbook = transformer.transformXLS(inputStream, beanParams);
		} else {
			workbook = transformer.transformMultipleSheetsList(inputStream, objects, newSheetNames, beanName,
					beanParams, 0);
		}

		response.setContentType("application/x-msdownload;charset=UTF-8");
		response.setHeader("Content-Disposition", "attachment; filename=" + filename);
		workbook.write(response.getOutputStream());
		response.getOutputStream().flush();
		logger.info("=============模板导出结束 =================");
	}

	/**
	 * 解决输出文件名的中文乱码问题
	 * 
	 * @param request
	 * @param outFileName
	 * @return
	 * @throws UnsupportedEncodingException
	 */
	private static String charEncoding(HttpServletRequest request, String outFileName)
			throws UnsupportedEncodingException {
		String agent = request.getHeader("USER-AGENT").toUpperCase();
		if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("MSIE"))
			outFileName = URLEncoder.encode(outFileName, "UTF-8");
		if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("RV:11"))
			outFileName = URLEncoder.encode(outFileName, "UTF-8");
		else if (!ObjectHelper.isEmpty(outFileName) && -1 != agent.indexOf("MOZILLA"))
			outFileName = new String(outFileName.getBytes(), "ISO8859-1");
		// outFileName = new String(outFileName.getBytes(), "UTF-8");
		else if (!ObjectHelper.isEmpty(agent) && -1 != agent.indexOf("OPERA"))
			outFileName = new String(outFileName.getBytes(), "ISO8859-1");
		return outFileName;
	}

	/**
	 * 获取变量值
	 * 
	 * @param mapperStr
	 * @param beanName
	 * @param beanVal
	 * @param isFast
	 * @return
	 * @throws Exception
	 */
	private static String getValueByBeanVal(String mapperStr, String beanName, Object beanVal, boolean isFast) {
		String regex = ".*?\\{(.+?)\\}";
		Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
		Matcher matchers = pattern.matcher(mapperStr);
		while (matchers.find()) {
			String tmpStr = matchers.group(1);
			mapperStr = mapperStr.replace("{" + tmpStr + "}",
					isFast ? invokeMethod(tmpStr, beanVal) : invokeMethod(tmpStr, beanName, beanVal));
		}
		return mapperStr;
	}

	/**
	 * 执行表达式
	 * 
	 * @param jexlExp
	 * @param beanName
	 * @param beanVal
	 * @return
	 */
	private static String invokeMethod(String jexlExp, String beanName, Object beanVal) {
		Object obj;
		try {
			Expression e = jexl.createExpression(jexlExp);
			JexlContext param = new MapContext();
			param.set(beanName, beanVal);
			obj = e.evaluate(param);
			if (null == obj) {
				return "";
			}
		} catch (Exception e) {
			logger.error("执行表达式异常:{}", jexlExp);
			return "";
		}
		return obj.toString();
	}

	/**
	 * 执行表达式(此方法更高效)
	 * 
	 * @param jexlExp
	 * @param beanVal
	 * @return
	 */
	private static String invokeMethod(String jexlExp, Object beanVal) {
		String[] jexlExpArr = jexlExp.split("\\.");
		if (jexlExpArr.length == 0) {
			return "";
		}
		if (jexlExpArr.length == 1) {
			return beanVal.toString();
		}
		Object obj = beanVal;
		try {
			for (int i = 1; i < jexlExpArr.length; i++) {
				Method method = obj.getClass().getMethod(jexlExpArr[i].replace("()", ""));
				obj = method.invoke(obj);
				if (obj == null) {
					return "";
				}
			}
		} catch (Exception e) {
			logger.error("执行表达式异常:{}", jexlExp);
			return "";
		}
		return obj.toString();
	}

	/**
	 * 获取导出数据MAP
	 * 
	 * @param mainTitle
	 * @param mapper
	 * @param beanName
	 * @param list
	 * @param isFast
	 * @return
	 * @throws Exception
	 */
	private static <T> Map<String, Object> getExportDataMap(String mainTitle, String mapper, String beanName,
			List<T> list, boolean isFast) {
		List<List<Object>> contentList = new ArrayList<List<Object>>();
		List<String> titleName = new ArrayList<String>();
		String[] mapperArr = mapper.split(",");

		for (int i = 0; i < mapperArr.length; i++) {
			titleName.add(mapperArr[i].substring(mapperArr[i].lastIndexOf("=") + 1));
		}

		for (int i = 0; i < list.size(); i++) {
			List<Object> content = new ArrayList<Object>();
			Object beanVal = list.get(i);
			for (int j = 0; j < mapperArr.length; j++) {
				content.add(getValueByBeanVal(mapperArr[j].substring(0, mapperArr[j].lastIndexOf("=")), beanName,
						beanVal, isFast));
			}
			contentList.add(content);
		}
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("mainTitle", mainTitle);
		param.put("titleName", titleName);
		param.put("contentList", contentList);
		return param;
	}
}
